﻿CREATE VIEW dbo.MaterielFull
AS
SELECT     CASE WHEN dbo.Materiel.Beskrivning IS NOT NULL THEN dbo.Materiel.Beskrivning ELSE dbo.MaterielTyp.Beskrivning END AS MaterielBeskrivning, 
                      dbo.Materiel.Antal AS MaterielAntal, dbo.Materiel.ByggnadId, dbo.Materiel.ValId, dbo.Byggnad.Benamning AS ByggnadBenamning, 
                      dbo.Byggnad.LeveransAdress AS ByggnadLeveransAdress, dbo.Byggnad.LeveransPostNr AS ByggnadLeveransPostNr, 
                      dbo.Byggnad.LeveransPostOrt AS ByggnadLeveransPostOrt, dbo.Byggnad.BesokAdress AS ByggnadBesokAdress, 
                      dbo.Byggnad.BesokPostNr AS ByggnadBesokPostNr, dbo.Byggnad.BesokPostOrt AS ByggnadBesokPostOrt, dbo.Stadsdel.Benamning AS ByggnadStadsdel, 
                      dbo.ByggnadTyp.Benamning AS ByggnadTyp, CASE (dbo.Byggnad.Inaktiv) WHEN 0 THEN 'Aktiv' WHEN 1 THEN 'Inaktiv' END AS ByggnadStatus, 
                      CASE (dbo.Byggnad.Inaktiv) 
                      WHEN 0 THEN '38A7CF73-0BFA-4F61-81E6-C1922F71F87D' WHEN 1 THEN '442F13BF-6ADA-43C1-BB88-828092B88C68' END AS ByggnadStatusID
FROM         dbo.Materiel LEFT OUTER JOIN
                      dbo.MaterielTyp ON dbo.MaterielTyp.ValId = dbo.Materiel.ValId AND dbo.MaterielTyp.Id = dbo.Materiel.MaterielTypId LEFT OUTER JOIN
                      dbo.Byggnad ON dbo.Materiel.ByggnadId = dbo.Byggnad.Id AND dbo.Materiel.ValId = dbo.Byggnad.ValId LEFT OUTER JOIN
                      dbo.ByggnadTyp ON dbo.Byggnad.TypId = dbo.ByggnadTyp.Id AND dbo.Byggnad.ValId = dbo.ByggnadTyp.ValId LEFT OUTER JOIN
                      dbo.Stadsdel ON dbo.Byggnad.StadsdelId = dbo.Stadsdel.Id AND dbo.Byggnad.ValId = dbo.Stadsdel.ValId
GO
EXECUTE sp_addextendedproperty @name = N'MS_DiagramPaneCount', @value = 2, @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'VIEW', @level1name = N'MaterielFull';




GO
EXECUTE sp_addextendedproperty @name = N'MS_DiagramPane1', @value = N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[34] 4[2] 2[2] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "Materiel"
            Begin Extent = 
               Top = 30
               Left = 505
               Bottom = 248
               Right = 656
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "MaterielTyp"
            Begin Extent = 
               Top = 53
               Left = 171
               Bottom = 274
               Right = 322
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "Byggnad"
            Begin Extent = 
               Top = 38
               Left = 832
               Bottom = 354
               Right = 1000
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "ByggnadTyp"
            Begin Extent = 
               Top = 240
               Left = 1202
               Bottom = 392
               Right = 1353
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "Stadsdel"
            Begin Extent = 
               Top = 2
               Left = 1195
               Bottom = 182
               Right = 1346
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 10
         Width = 284
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column =', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'VIEW', @level1name = N'MaterielFull';












GO
EXECUTE sp_addextendedproperty @name = N'MS_DiagramPane2', @value = N'2070
         Alias = 1710
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'VIEW', @level1name = N'MaterielFull';









